package dao;

import vo.Course;
import vo.Score;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ScoreDao {
    private Connection conn = null;

    public boolean deleteScore(String id) throws Exception {
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "delete from score where id='" + id + "'";
        int i = stat.executeUpdate(sql);
        closeConnection();
        return i == 1;
    }

    //数据库连接
    private void initConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentManagerDB?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true", "StuManager", "123456");
    }

    private void closeConnection() throws Exception {
        conn.close();
    }

    public ArrayList<Score> findAll() throws Exception {
        ArrayList<Score> al = new ArrayList<>();
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from score";
        ResultSet rs = stat.executeQuery(sql);
        getAllCourse(al, rs);
        closeConnection();
        return al;
    }

    public void updateScoreInfo(String course_name, Integer score, String level, String stu_id,String id) throws Exception {

        initConnection();
        String sql = "update score set course_name=?, score=?, level=?,stu_id=? where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, course_name);
        ps.setInt(2, score);
        ps.setString(3, level);
        ps.setString(4, stu_id);
        ps.setInt(5, Integer.valueOf(id));
        ps.executeUpdate();
        closeConnection();
    }

    private void getAllCourse(ArrayList<Score> al, ResultSet rs) throws Exception {
        while (rs.next()) {
            Score s = new Score();
            s.setId(rs.getInt("id"));
            s.setScore(rs.getInt("score"));
            s.setCourse_name(rs.getString("course_name"));
            s.setLevel(rs.getString("level"));
            s.setStu_id(rs.getString("stu_id"));
            al.add(s);

        }
    }

    public ArrayList<Score> getOnePage(int page, int size) throws Exception {
        ArrayList<Score> al = new ArrayList<>();
        initConnection();
        String sql = "SELECT * FROM score limit ?, ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, (page - 1) * size);
        ps.setInt(2, size);
        ResultSet rs = ps.executeQuery();
        getAllCourse(al, rs);
        closeConnection();
        return al;
    }

    public int getCount() throws Exception {
        initConnection();
        String sql = "select count(*) from score";
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(sql);
        rs.next();
        int count = rs.getInt(1);
        closeConnection();
        return count;

    }

    public Score findWithId(String id) throws Exception {
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from score where id = '" + id + "'";
        ResultSet rs = stat.executeQuery(sql);
        Score score = getScore(rs);
        closeConnection();
        return score;
    }

    private Score getScore(ResultSet rs) throws Exception {
        Score score = null;
        if (rs.next()) {
            score = new Score();
            score.setId(rs.getInt("id"));
            score.setCourse_name(rs.getString("course_name"));
            score.setScore(rs.getInt("score"));
            score.setLevel(rs.getString("level"));
            score.setStu_id(rs.getString("stu_id"));
        }
        return score;
    }

    public boolean insertScore(String id, String course_name, String score, String level,String stu_id) throws Exception {
        initConnection();
        String sql = "insert into score(id,course_name, score, level,stu_id) values(?, ?, ?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, id);
        ps.setString(2, course_name);
        ps.setString(3, score);
        ps.setString(4, level);
        ps.setString(5,stu_id);
        int i = ps.executeUpdate();
        closeConnection();
        return i == 1;
    }

    public List<Score> findWithStuId(String stu_id) throws Exception{
        initConnection();
        Statement stat = conn.createStatement();
        String sql = "select * from score where stu_id = '" + stu_id + "'";
        ResultSet rs = stat.executeQuery(sql);
        ArrayList<Score> list = new ArrayList<>();
        getAllCourse(list,rs);
        closeConnection();
        return list;
    }
}
